﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

/***********************************************************
* Description: SQL CLR
* Date:   2013-08-15 09:59
* Author: 刘俊
*
*    Created By: 刘俊
*  Created Date: 2013-08-15
*      Comments: 创建正则表达式匹配字符串函数。返回匹配正则表达式的字符串位置。
************************************************************
*  __                        _____                     
* /\ \       __             /\___ \                    
* \ \ \     /\_\  __  __    \/__/\ \  __  __    ___    
*  \ \ \  __\/\ \/\ \/\ \      _\ \ \/\ \/\ \ /' _ `\  
*   \ \ \LJ \\ \ \ \ \_\ \    /\ \_\ \ \ \_\ \/\ \/\ \ 
*    \ \____/ \ \_\ \____/    \ \____/\ \____/\ \_\ \_\
*     \/___/   \/_/\/___/      \/___/  \/___/  \/_/\/_/
************************************************************/

public partial class UserDefinedFunctions
{
    /// <summary>
    /// 检索字符串，返回匹配正则表达式的字符串位置
    /// </summary>
    /// <param name="source">源字符串</param>
    /// <param name="regex">正则表达式</param>
    /// <param name="start">字符串起始位置</param>
    /// <param name="num">指定返回第几个匹配</param>
    /// <returns>字符串位置</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 Regexp_inStr(string source, string regex,int start,int num)
    {
        MatchCollection matches = Regex.Matches(source.Substring(start), regex);
        SqlInt32 returnvalue= new SqlInt32();

        if (matches.Count > 0)
        {
            for (int i = 0; i < matches.Count; i++)
            {
                if (i == num-1)
                {
                    returnvalue= matches[i].Index+1;
                }
            }
        }
        return returnvalue;  
    }


    /// <summary>
    /// （SQL不支持重载，故重命名之）重载Regexp_inStr,起始位置为0，匹配第一个字符串
    /// </summary>
    /// <param name="source"></param>
    /// <param name="regex"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 Regexp_inStr2(string source, string regex)
    {
        return Regexp_inStr(source, regex,0,1);
    }
};

